#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

dim_tb_order_detail_full="
USE jtp_gd07_warehouse;
WITH a AS (
    SELECT order_id,
           user_id,
           shop_id,
           product_id,
           product_quantity,
           pay_amount,
           pay_time,
           order_status,
           refund_amount,
           refund_time
    FROM jtp_gd07_warehouse.ods_tb_order_detail
    WHERE dt = '${data_date}'
),b AS (
    SELECT user_id,
           username,
           register_time,
           is_new,
           age,
           city,
           member_level
    FROM jtp_gd07_warehouse.ods_tb_user
),c AS (
    SELECT shop_id,
           shop_name,
           category,
           seller_id,
           create_time,
           status,
           address
    FROM jtp_gd07_warehouse.ods_tb_shop
),d AS (
    SELECT product_id,
           shop_id,
           product_name,
           category,
           price,
           stock,
           status,
           create_time,
           update_time
    FROM jtp_gd07_warehouse.ods_tb_product
),e AS (
    SELECT comment_id,
           product_id,
           user_id,
           order_id,
           content,
           score,
           has_pic,
           comment_time,
           reply_content
    FROM jtp_gd07_warehouse.ods_tb_comment
)
INSERT OVERWRITE TABLE jtp_gd07_warehouse.dim_tb_order_detail_full PARTITION (dt = '${data_date}')
SELECT
    a.order_id,
    a.user_id,
    CASE
        WHEN b.is_new = 0 THEN '新用户'
        WHEN b.is_new = 1 THEN '普通用户'
        WHEN b.is_new = 2 THEN '老用户'
        ELSE CAST(b.is_new AS STRING)
        END AS is_new,
    a.shop_id,
    c.shop_name,
    a.product_id,
    d.product_name,
    d.price,
    e.score,
    e.has_pic,
    a.product_quantity,
    a.pay_amount,
    CAST(a.pay_time AS TIMESTAMP) AS pay_time,
    CASE
        WHEN a.order_status = 1 THEN '待支付'
        WHEN a.order_status = 2 THEN '已支付'
        WHEN a.order_status = 3 THEN '已发货'
        WHEN a.order_status = 4 THEN '已完成'
        WHEN a.order_status = 5 THEN '已退款'
        ELSE CAST(a.order_status AS STRING)
        END AS order_status,
    a.refund_amount,
    CAST(a.refund_time AS TIMESTAMP) AS  refund_time
FROM a
         LEFT JOIN b ON a.user_id = b.user_id
         LEFT JOIN c ON a.shop_id = c.shop_id
         LEFT JOIN d ON a.product_id = d.product_id
         LEFT JOIN e ON a.order_id = e.order_id
WHERE b.user_id IS NOT NULL
  AND c.shop_id IS NOT NULL
  AND d.product_id IS NOT NULL
  AND e.order_id IS NOT NULL;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${dim_tb_order_detail_full}"